﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using TarreOA.TarreOAModels;

namespace TarreOA.TarreOADAL
{
    public class UserCommunicationGroupLinkMantbDAO
    {
        /// <summary>
        /// 为指定通讯组添加联系人
        /// </summary>
        /// <param name="comGroupId"></param>
        /// <param name="groupLinkManName"></param>
        /// <param name="groupLinkManSec"></param>
        /// <param name="groupLinkManBirthday"></param>
        /// <param name="groupLinkManName2"></param>
        /// <param name="groupLinkManDurty"></param>
        /// <param name="groupLinkManUnitName"></param>
        /// <param name="groupLinkManUnitAddress"></param>
        /// <param name="groupLinkManUnitTelephone"></param>
        /// <param name="groupLinkManUnitFacsimile"></param>
        /// <param name="groupLinkManHomeAddress"></param>
        /// <param name="groupLinkManHomeTelephone"></param>
        /// <param name="groupLinkManHandset"></param>
        /// <param name="groupLinkManEmail"></param>
        /// <param name="groupLinkManOicq"></param>
        /// <param name="groupLinkManMsn"></param>
        /// <param name="groupLinkManRemark"></param>
        /// <param name="UserLoginName"></param>
        /// <returns></returns>
        public int saveComGroupLinkManInfo(int comGroupId, string groupLinkManName, int groupLinkManSec, DateTime groupLinkManBirthday
            , string groupLinkManName2, string groupLinkManDurty, string groupLinkManUnitName, string groupLinkManUnitAddress
            , string groupLinkManUnitTelephone, string groupLinkManUnitFacsimile, string groupLinkManHomeAddress
            , string groupLinkManHomeTelephone, string groupLinkManHandset, string groupLinkManEmail, string groupLinkManOicq
            , string groupLinkManMsn, string groupLinkManRemark, string userLoginName)
        {
            int count = 0;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "insert into UserCommunicationGroupLinkMan values(@cgid, @glmn, @glms, @glmb, @glmn2, @glmd, @glmun" + 
                    ", @glmua, @glmut, @glmuf, @glmha, @glmht, @glmhs, @glme, @glmO, @glmM, @glmR, @uln)";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@cgid", comGroupId));
                sqlCmd.Parameters.Add(new SqlParameter("@glmn", groupLinkManName));
                sqlCmd.Parameters.Add(new SqlParameter("@glms", groupLinkManSec));
                sqlCmd.Parameters.Add(new SqlParameter("@glmb", groupLinkManBirthday));
                sqlCmd.Parameters.Add(new SqlParameter("@glmn2", groupLinkManName2));
                sqlCmd.Parameters.Add(new SqlParameter("@glmd", groupLinkManDurty));
                sqlCmd.Parameters.Add(new SqlParameter("@glmun", groupLinkManUnitName));
                sqlCmd.Parameters.Add(new SqlParameter("@glmua", groupLinkManUnitAddress));
                sqlCmd.Parameters.Add(new SqlParameter("@glmut", groupLinkManUnitTelephone));
                sqlCmd.Parameters.Add(new SqlParameter("@glmuf", groupLinkManUnitFacsimile));
                sqlCmd.Parameters.Add(new SqlParameter("@glmha", groupLinkManHomeAddress));
                sqlCmd.Parameters.Add(new SqlParameter("@glmht", groupLinkManHomeTelephone));
                sqlCmd.Parameters.Add(new SqlParameter("@glmhs", groupLinkManHandset));
                sqlCmd.Parameters.Add(new SqlParameter("@glme", groupLinkManEmail));
                sqlCmd.Parameters.Add(new SqlParameter("@glmO", groupLinkManOicq));
                sqlCmd.Parameters.Add(new SqlParameter("@glmM", groupLinkManMsn));
                sqlCmd.Parameters.Add(new SqlParameter("@glmR", groupLinkManRemark));
                sqlCmd.Parameters.Add(new SqlParameter("@uln", userLoginName));
                count = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }

        /// <summary>
        /// 根据通讯组编号查找该通讯组的联系人信息
        /// </summary>
        /// <param name="cgid"></param>
        /// <returns></returns>
        public List<UserCommunicationGroupLinkMantb> findByComGroupId(int cgid)
        {
            List<UserCommunicationGroupLinkMantb> list = null;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from UserCommunicationGroupLinkMan where ComGroupId = @cgid";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@cgid", cgid));
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr.HasRows)
                {
                    list = new List<UserCommunicationGroupLinkMantb>();
                    UserDAO userDAO = new UserDAO();
                    UserCommunicationGrouptbDAO ucgDAO = new UserCommunicationGrouptbDAO();
                    while (sqlDr.Read())
                    {
                        UserCommunicationGroupLinkMantb ucgLman = new UserCommunicationGroupLinkMantb();
                        ucgLman.GroupLinkManId = (int)sqlDr["GroupLinkManId"];
                        ucgLman.ComGroup = ucgDAO.findByComGroupId((int)sqlDr["ComGroupId"]);
                        ucgLman.GroupLinkManName = sqlDr["GroupLinkManName"].ToString();
                        ucgLman.GroupLinkManSec = (int)sqlDr["GroupLinkManSec"];
                        ucgLman.GroupLinkManBirthday = (DateTime)sqlDr["GroupLinkManBirthday"];
                        ucgLman.GroupLinkManName2 = sqlDr["GroupLinkManName2"].ToString();
                        ucgLman.GroupLinkManDuty = sqlDr["GroupLinkManDuty"].ToString();
                        ucgLman.GroupLinkManUnitName = sqlDr["GroupLinkManUnitName"].ToString();
                        ucgLman.GroupLinkManUnitAddress = sqlDr["GroupLinkManUnitAddress"].ToString();
                        ucgLman.GroupLinkManUnitTelephone = sqlDr["GroupLinkManUnitTelephone"].ToString();
                        ucgLman.GroupLinkManUnitFacsimile = sqlDr["GroupLinkManUnitFacsimile"].ToString();
                        ucgLman.GroupLinkManHomeAddress = sqlDr["GroupLinkManHomeAddress"].ToString();
                        ucgLman.GroupLinkManHomeTelephone = sqlDr["GroupLinkManHomeTelephone"].ToString();
                        ucgLman.GroupLinkManHandset = sqlDr["GroupLinkManHandset"].ToString();
                        ucgLman.GroupLinkManEmail = sqlDr["GroupLinkManEmail"].ToString();
                        ucgLman.GroupLinkManOicq = sqlDr["GroupLinkManOicq"].ToString();
                        ucgLman.GroupLinkManMsn = sqlDr["GroupLinkManMsn"].ToString();
                        ucgLman.GroupLinkManRemark = sqlDr["GroupLinkManRemark"].ToString();
                        ucgLman.CurUser = userDAO.FindByLoginName(sqlDr["CurUserLoginName"].ToString());
                        list.Add(ucgLman);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }

        /// <summary>
        /// 根据通讯组联系人编号查找联系人信息
        /// </summary>
        /// <param name="cgLmid"></param>
        /// <returns></returns>
        public UserCommunicationGroupLinkMantb findByGroupLinkManId(int cgLmid)
        {
            UserCommunicationGroupLinkMantb ucgLman = null;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from UserCommunicationGroupLinkMan where GroupLinkManId = @cgLmid";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@cgLmid", cgLmid));
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr.HasRows)
                {
                    UserDAO userDAO = new UserDAO();
                    UserCommunicationGrouptbDAO ucgDAO = new UserCommunicationGrouptbDAO();
                    ucgLman = new UserCommunicationGroupLinkMantb();
                    sqlDr.Read();
                    ucgLman.GroupLinkManId = (int)sqlDr["GroupLinkManId"];
                    ucgLman.ComGroup = ucgDAO.findByComGroupId((int)sqlDr["ComGroupId"]);
                    ucgLman.GroupLinkManName = sqlDr["GroupLinkManName"].ToString();
                    ucgLman.GroupLinkManSec = (int)sqlDr["GroupLinkManSec"];
                    ucgLman.GroupLinkManBirthday = (DateTime)sqlDr["GroupLinkManBirthday"];
                    ucgLman.GroupLinkManName2 = sqlDr["GroupLinkManName2"].ToString();
                    ucgLman.GroupLinkManDuty = sqlDr["GroupLinkManDuty"].ToString();
                    ucgLman.GroupLinkManUnitName = sqlDr["GroupLinkManUnitName"].ToString();
                    ucgLman.GroupLinkManUnitAddress = sqlDr["GroupLinkManUnitAddress"].ToString();
                    ucgLman.GroupLinkManUnitTelephone = sqlDr["GroupLinkManUnitTelephone"].ToString();
                    ucgLman.GroupLinkManUnitFacsimile = sqlDr["GroupLinkManUnitFacsimile"].ToString();
                    ucgLman.GroupLinkManHomeAddress = sqlDr["GroupLinkManHomeAddress"].ToString();
                    ucgLman.GroupLinkManHomeTelephone = sqlDr["GroupLinkManHomeTelephone"].ToString();
                    ucgLman.GroupLinkManHandset = sqlDr["GroupLinkManHandset"].ToString();
                    ucgLman.GroupLinkManEmail = sqlDr["GroupLinkManEmail"].ToString();
                    ucgLman.GroupLinkManOicq = sqlDr["GroupLinkManOicq"].ToString();
                    ucgLman.GroupLinkManMsn = sqlDr["GroupLinkManMsn"].ToString();
                    ucgLman.GroupLinkManRemark = sqlDr["GroupLinkManRemark"].ToString();
                    ucgLman.CurUser = userDAO.FindByLoginName(sqlDr["UserLoginName"].ToString());
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return ucgLman;
        }

        /// <summary>
        /// 根据通讯组联系人编号删除联系人信息
        /// </summary>
        /// <param name="cgLmid"></param>
        /// <returns></returns>
        public int delComGroupLinkManByGroupLinkManId(int cgLmid)
        {
            int count = 0;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "delete from UserCommunicationGroupLinkMan where GroupLinkManId = @cgLmid";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@cgLmid", cgLmid));
                count = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally 
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }

        /// <summary>
        /// 根据通讯组联系人编号更改联系人信息
        /// </summary>
        /// <param name="cgLmid"></param>
        /// <param name="cgid"></param>
        /// <param name="cgLmName"></param>
        /// <param name="cgLmSec"></param>
        /// <param name="cgLmBirthday"></param>
        /// <param name="cgLmName2"></param>
        /// <param name="cgLmDuty"></param>
        /// <param name="cgLmUnitName"></param>
        /// <param name="cgLmUnitAddress"></param>
        /// <param name="cgLmUnitTelephone"></param>
        /// <param name="cgLmUnitFacsimile"></param>
        /// <param name="cgLmHomeAddress"></param>
        /// <param name="cgLmHomeTelephone"></param>
        /// <param name="cgLmHandset"></param>
        /// <param name="cgLmEmail"></param>
        /// <param name="cgLmOicq"></param>
        /// <param name="cgLmMsn"></param>
        /// <param name="cgLmRemark"></param>
        /// <returns></returns>
        public int updateComGroupLinkManInfoByGroupLinkManId(int cgLmid, int cgid, string cgLmName, int cgLmSec, DateTime cgLmBirthday,
            string cgLmName2, string cgLmDuty, string cgLmUnitName, string cgLmUnitAddress, string cgLmUnitTelephone,
            string cgLmUnitFacsimile, string cgLmHomeAddress, string cgLmHomeTelephone, string cgLmHandset, string cgLmEmail,
            string cgLmOicq, string cgLmMsn, string cgLmRemark)
        {
            int count = 0;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "update UserCommunicationGroupLinkMan set ComGroupId = @cgid, GroupLinkManName = @cgLmName, "
                    + " GroupLinkManSec = @cgLmSec, GroupLinkManBirthday = @cgLmBirthday, GroupLinkManName2 = @cgLmName2,"
                    + " GroupLinkManDuty = @cgLmDuty, GroupLinkManUnitName = @cgLmUnitName, GroupLinkManUnitAddress = @cgLmUnitAddress                    , GroupLinkManUnitTelephone = @cgLmUnitTelephone, GroupLinkManUnitFacsimile = @cgLmUnitFacsimile,                                                           GroupLinkManHomeAddress = @cgLmHomeAddress, GroupLinkManHomeTelephone = @cgLmHomeTelephone,                                                GroupLinkManHandset = @cgLmHandset, GroupLinkManEmail = @cgLmEmail, GroupLinkManOicq = @cgLmOicq,                                                 GroupLinkManMsn = @cgLmMsn, GroupLinkManRemark = @cgLmRemark where GroupLinkManId = @cgLmid";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@cgid", cgid));
                sqlCmd.Parameters.Add(new SqlParameter("@cgLmName", cgLmName));
                sqlCmd.Parameters.Add(new SqlParameter("@cgLmSec", cgLmSec));
                sqlCmd.Parameters.Add(new SqlParameter("@cgLmBirthday", cgLmBirthday));
                sqlCmd.Parameters.Add(new SqlParameter("@cgLmName2", cgLmName2));
                sqlCmd.Parameters.Add(new SqlParameter("@cgLmDuty", cgLmDuty));
                sqlCmd.Parameters.Add(new SqlParameter("@cgLmUnitName", cgLmUnitName));
                sqlCmd.Parameters.Add(new SqlParameter("@cgLmUnitAddress", cgLmUnitAddress));
                sqlCmd.Parameters.Add(new SqlParameter("@cgLmUnitTelephone", cgLmUnitTelephone));
                sqlCmd.Parameters.Add(new SqlParameter("@cgLmUnitFacsimile", cgLmUnitFacsimile));
                sqlCmd.Parameters.Add(new SqlParameter("@cgLmHomeAddress", cgLmHomeAddress));
                sqlCmd.Parameters.Add(new SqlParameter("@cgLmHomeTelephone", cgLmHomeTelephone));
                sqlCmd.Parameters.Add(new SqlParameter("@cgLmHandset", cgLmHandset));
                sqlCmd.Parameters.Add(new SqlParameter("@cgLmEmail", cgLmEmail));
                sqlCmd.Parameters.Add(new SqlParameter("@cgLmOicq", cgLmOicq));
                sqlCmd.Parameters.Add(new SqlParameter("@cgLmMsn", cgLmMsn));
                sqlCmd.Parameters.Add(new SqlParameter("@cgLmRemark", cgLmRemark));
                sqlCmd.Parameters.Add(new SqlParameter("@cgLmid", cgLmid));
                count = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }

        /// <summary>
        /// 根据联系人姓名查找当前用户的联系人信息
        /// </summary>
        /// <param name="lmName"></param>
        /// <param name="uln"></param>
        /// <returns></returns>
        public List<UserCommunicationGroupLinkMantb> findByLinkManNameAndUserLoginName(string lmName, string uln)
        {
            List<UserCommunicationGroupLinkMantb> list = null;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from UserCommunicationGroupLinkMan where GroupLinkManName like @lmName and CurUserLoginName = @uln";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@lmName", "%" + lmName + "%"));
                sqlCmd.Parameters.Add(new SqlParameter("@uln", uln));
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr.HasRows)
                {
                    list = new List<UserCommunicationGroupLinkMantb>();
                    UserDAO userDAO = new UserDAO();
                    UserCommunicationGrouptbDAO ucgDAO = new UserCommunicationGrouptbDAO();
                    while (sqlDr.Read())
                    {
                        UserCommunicationGroupLinkMantb ucgLman = new UserCommunicationGroupLinkMantb();
                        ucgLman.GroupLinkManId = (int)sqlDr["GroupLinkManId"];
                        ucgLman.ComGroup = ucgDAO.findByComGroupId((int)sqlDr["ComGroupId"]);
                        ucgLman.GroupLinkManName = sqlDr["GroupLinkManName"].ToString();
                        ucgLman.GroupLinkManSec = (int)sqlDr["GroupLinkManSec"];
                        ucgLman.GroupLinkManBirthday = (DateTime)sqlDr["GroupLinkManBirthday"];
                        ucgLman.GroupLinkManName2 = sqlDr["GroupLinkManName2"].ToString();
                        ucgLman.GroupLinkManDuty = sqlDr["GroupLinkManDuty"].ToString();
                        ucgLman.GroupLinkManUnitName = sqlDr["GroupLinkManUnitName"].ToString();
                        ucgLman.GroupLinkManUnitAddress = sqlDr["GroupLinkManUnitAddress"].ToString();
                        ucgLman.GroupLinkManUnitTelephone = sqlDr["GroupLinkManUnitTelephone"].ToString();
                        ucgLman.GroupLinkManUnitFacsimile = sqlDr["GroupLinkManUnitFacsimile"].ToString();
                        ucgLman.GroupLinkManHomeAddress = sqlDr["GroupLinkManHomeAddress"].ToString();
                        ucgLman.GroupLinkManHomeTelephone = sqlDr["GroupLinkManHomeTelephone"].ToString();
                        ucgLman.GroupLinkManHandset = sqlDr["GroupLinkManHandset"].ToString();
                        ucgLman.GroupLinkManEmail = sqlDr["GroupLinkManEmail"].ToString();
                        ucgLman.GroupLinkManOicq = sqlDr["GroupLinkManOicq"].ToString();
                        ucgLman.GroupLinkManMsn = sqlDr["GroupLinkManMsn"].ToString();
                        ucgLman.GroupLinkManRemark = sqlDr["GroupLinkManRemark"].ToString();
                        ucgLman.CurUser = userDAO.FindByLoginName(sqlDr["CurUserLoginName"].ToString());
                        list.Add(ucgLman);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }

        /// <summary>
        /// 根据联系人生日查询当前用户的联系人
        /// </summary>
        /// <param name="startDate"></param>
        /// <param name="endDate"></param>
        /// <param name="uln"></param>
        /// <returns></returns>
        public List<UserCommunicationGroupLinkMantb> findByBirthdayAndUserLoginName(string startDate, string endDate, string uln)
        {
            List<UserCommunicationGroupLinkMantb> list = null;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from UserCommunicationGroupLinkMan where GroupLinkManBirthday > @startDate and GroupLinkManBirthday < @endDate and CurUserLoginName = @uln";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@startDate", startDate));
                sqlCmd.Parameters.Add(new SqlParameter("@endDate", endDate));
                sqlCmd.Parameters.Add(new SqlParameter("@uln", uln));
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr.HasRows)
                {
                    list = new List<UserCommunicationGroupLinkMantb>();
                    UserDAO userDAO = new UserDAO();
                    UserCommunicationGrouptbDAO ucgDAO = new UserCommunicationGrouptbDAO();
                    while (sqlDr.Read())
                    {
                        UserCommunicationGroupLinkMantb ucgLman = new UserCommunicationGroupLinkMantb();
                        ucgLman.GroupLinkManId = (int)sqlDr["GroupLinkManId"];
                        ucgLman.ComGroup = ucgDAO.findByComGroupId((int)sqlDr["ComGroupId"]);
                        ucgLman.GroupLinkManName = sqlDr["GroupLinkManName"].ToString();
                        ucgLman.GroupLinkManSec = (int)sqlDr["GroupLinkManSec"];
                        ucgLman.GroupLinkManBirthday = (DateTime)sqlDr["GroupLinkManBirthday"];
                        ucgLman.GroupLinkManName2 = sqlDr["GroupLinkManName2"].ToString();
                        ucgLman.GroupLinkManDuty = sqlDr["GroupLinkManDuty"].ToString();
                        ucgLman.GroupLinkManUnitName = sqlDr["GroupLinkManUnitName"].ToString();
                        ucgLman.GroupLinkManUnitAddress = sqlDr["GroupLinkManUnitAddress"].ToString();
                        ucgLman.GroupLinkManUnitTelephone = sqlDr["GroupLinkManUnitTelephone"].ToString();
                        ucgLman.GroupLinkManUnitFacsimile = sqlDr["GroupLinkManUnitFacsimile"].ToString();
                        ucgLman.GroupLinkManHomeAddress = sqlDr["GroupLinkManHomeAddress"].ToString();
                        ucgLman.GroupLinkManHomeTelephone = sqlDr["GroupLinkManHomeTelephone"].ToString();
                        ucgLman.GroupLinkManHandset = sqlDr["GroupLinkManHandset"].ToString();
                        ucgLman.GroupLinkManEmail = sqlDr["GroupLinkManEmail"].ToString();
                        ucgLman.GroupLinkManOicq = sqlDr["GroupLinkManOicq"].ToString();
                        ucgLman.GroupLinkManMsn = sqlDr["GroupLinkManMsn"].ToString();
                        ucgLman.GroupLinkManRemark = sqlDr["GroupLinkManRemark"].ToString();
                        ucgLman.CurUser = userDAO.FindByLoginName(sqlDr["CurUserLoginName"].ToString());
                        list.Add(ucgLman);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }

        /// <summary>
        /// 根据用户的单位关键字查找用户联系人信息
        /// </summary>
        /// <param name="lmUnitName"></param>
        /// <param name="uln"></param>
        /// <returns></returns>
        public List<UserCommunicationGroupLinkMantb> findByLinkManUnitNameAndUserLoginName(string lmUnitName, string uln)
        {
            List<UserCommunicationGroupLinkMantb> list = null;
            SqlConnection sqlConn = null;
            SqlCommand sqlCmd = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from UserCommunicationGroupLinkMan where GroupLinkManUnitName like @lmName and CurUserLoginName = @uln";
                sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@lmName", "%" + lmUnitName + "%"));
                sqlCmd.Parameters.Add(new SqlParameter("@uln", uln));
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr.HasRows)
                {
                    list = new List<UserCommunicationGroupLinkMantb>();
                    UserDAO userDAO = new UserDAO();
                    UserCommunicationGrouptbDAO ucgDAO = new UserCommunicationGrouptbDAO();
                    while (sqlDr.Read())
                    {
                        UserCommunicationGroupLinkMantb ucgLman = new UserCommunicationGroupLinkMantb();
                        ucgLman.GroupLinkManId = (int)sqlDr["GroupLinkManId"];
                        ucgLman.ComGroup = ucgDAO.findByComGroupId((int)sqlDr["ComGroupId"]);
                        ucgLman.GroupLinkManName = sqlDr["GroupLinkManName"].ToString();
                        ucgLman.GroupLinkManSec = (int)sqlDr["GroupLinkManSec"];
                        ucgLman.GroupLinkManBirthday = (DateTime)sqlDr["GroupLinkManBirthday"];
                        ucgLman.GroupLinkManName2 = sqlDr["GroupLinkManName2"].ToString();
                        ucgLman.GroupLinkManDuty = sqlDr["GroupLinkManDuty"].ToString();
                        ucgLman.GroupLinkManUnitName = sqlDr["GroupLinkManUnitName"].ToString();
                        ucgLman.GroupLinkManUnitAddress = sqlDr["GroupLinkManUnitAddress"].ToString();
                        ucgLman.GroupLinkManUnitTelephone = sqlDr["GroupLinkManUnitTelephone"].ToString();
                        ucgLman.GroupLinkManUnitFacsimile = sqlDr["GroupLinkManUnitFacsimile"].ToString();
                        ucgLman.GroupLinkManHomeAddress = sqlDr["GroupLinkManHomeAddress"].ToString();
                        ucgLman.GroupLinkManHomeTelephone = sqlDr["GroupLinkManHomeTelephone"].ToString();
                        ucgLman.GroupLinkManHandset = sqlDr["GroupLinkManHandset"].ToString();
                        ucgLman.GroupLinkManEmail = sqlDr["GroupLinkManEmail"].ToString();
                        ucgLman.GroupLinkManOicq = sqlDr["GroupLinkManOicq"].ToString();
                        ucgLman.GroupLinkManMsn = sqlDr["GroupLinkManMsn"].ToString();
                        ucgLman.GroupLinkManRemark = sqlDr["GroupLinkManRemark"].ToString();
                        ucgLman.CurUser = userDAO.FindByLoginName(sqlDr["CurUserLoginName"].ToString());
                        list.Add(ucgLman);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }
    }
}
